﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
namespace syc.Sale
{
    public partial class 月原辅材料采购计划完成情况 : System.Web.UI.Page
    {
        protected int month;
        protected int year;
        protected DateTime d1, d2;//d1月初日期，d2月末日期
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                this.Year0.SelectedValue = DateTime.Today.Year.ToString();
                this.Month0.SelectedValue = DateTime.Today.Month.ToString();
            }
        }
        public void GetData(string yy, string mm)
        {
            SqlConnection Conn = new SqlConnection();
            Conn.ConnectionString = ConfigurationManager.ConnectionStrings["SCMConnectionString1"].ConnectionString;
            DataSet ds = new DataSet();
            SqlDataAdapter sda = new SqlDataAdapter("select 月初日期,月末日期 from 供应处生产月设置 where 年度='" + yy + "' and 月份='" + mm + "'", Conn);
            sda.Fill(ds);
            try
            {
                d1 = DateTime.Parse(ds.Tables[0].Rows[0][0].ToString());
                d2 = DateTime.Parse(ds.Tables[0].Rows[0][1].ToString());
                this.Message.Text = ".查看成功";        
            }
            catch
            {
                this.Message.Text = ".该扎帐月没有设置";
                return;
            }
        }
        protected void Button_Query_Click(object sender, EventArgs e)
        {
            GetData(this.Year0.SelectedValue, this.Month0.SelectedValue);
            if (this.Message.Text != ".该扎帐月没有设置")
            {
                string sql = @"select 0 as tip,年度,月份,品名,规格,单位,实际购进量,计划购进量,((实际购进量)/计划购进量)*100 as 计划完成 from 
(select 0 as tip,年度,月份,原料.ID,品名,规格,单位,计划购进量 from 原料 left join 原料采购计划0 on 原料.ID=原料采购计划0.原料ID) t0
left join
(select 管材ID,SUM(管材到货登记.磅量) as 实际购进量 from 管材到货登记 inner join 管材到货明细 on 管材到货明细.管材到货登记ID=管材到货登记.ID  where 管材到货登记.到货日期 >='@d1' and 管材到货登记.到货日期<='@d2' group by 管材到货明细.管材ID ) t1
on t0.ID=t1.管材ID  where 年度=@年度 and 月份=@月份
union 
select 1 as tip,年度,月份,品名,规格,单位,实际购进量,计划购进量,((实际购进量)/计划购进量)*100 as 计划完成 from 
(select 1 as tip,年度,月份,材料.ID,品名,规格,单位,计划购进量  from 材料 left join 辅料采购计划0 on 材料.ID=辅料采购计划0.辅料ID ) t0
left join
(select 材料ID,SUM(数量) as 实际购进量 from 材料到发货 where 材料到发货.日期 >='@d1' and 材料到发货.日期<='@d2'  group by 材料ID) t1
on t0.ID=t1.材料ID where 年度=@年度 and 月份=@月份 ";
                sql = sql.Replace("@年度", this.Year0.SelectedValue);
                sql = sql.Replace("@月份", this.Month0.SelectedValue);
                sql = sql.Replace("@d1", d1.ToString());
                sql = sql.Replace("@d2", d2.ToString());
                this.SqlDataSource1.SelectCommand = sql;
                this.ViewState["SelectCommand"] = this.SqlDataSource1.SelectCommand;
                this.Message.Text = ".查看成功";
            }
        }

        protected void Button_Save_Click(object sender, EventArgs e)
        {

        }
    }
}
